<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class StatisticsOtherDept extends AdminController {

	function __construct() {
		parent::__construct();
		$this -> load-> model('Student_feedback_model');
		$this -> load-> model('Supervisor_feedback_model');
		$this -> load-> model('Admin_model');
		$this -> load-> model('Table1_model');
		$this -> load-> model('Table2_model');
		$this -> load-> model('Table3_model');
		$this -> load-> model('Table4_model');
		$this -> load-> model('Table5_model');
		$this -> load-> model('Table6_model');
	}

	function Index() {	
		$this->load->view('admin/statisticsOtherDeptList');
	}

	function getDatas($params = array(), $isExport = false) {
		$connar = array();
		if (!$isExport) {
			$params = $_POST;
			$connar = $this -> Admin_model -> pickPages($connar, $params);
		}
		// $connar['items']="id,dept_id,dept_name";
		// $connar['where']=$this->sqlEscape('status<>?? and  dept_id is not null',array(CommonStatus::Deleted[XPHEnum::Value]));
		// $connar['groupby']='dept_id';
		$where='where db_admin.status<>'.CommonStatus::Deleted[XPHEnum::Value].' and  db_admin.dept_id is not null and db_admin.dept_id<>0 AND (
	db_table1.admin_id IS NOT NULL
	OR db_table2.admin_id IS NOT NULL
	OR db_table3.admin_id IS NOT NULL
	OR db_table4.admin_id IS NOT NULL
	OR db_table5.admin_id IS NOT NULL
	OR db_table6.admin_id IS NOT NULL
) and db_admin.dept_id<>0';
	$role=explode(',',getSess()['sub_role_id']);
	array_push($role,getSess()['role_id']);

	if(in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
		$where.=' and 1=1';
	}else{
		$where.=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
	}
		if (isset($params['start_time']) && isset($params['end_time'])) {
			// $where.=' and created>'.strtotime($params['start_time']).' and created<'.strtotime($params['end_time']);
			// $where.=$this->sqlEscape(" and db_table1.created>=?? and db_table1.created<??  and db_table2.created>=?? and db_table2.created<?? and db_table3.created>=?? and db_table3.created<?? and db_table4.created>=?? and db_table4.created<?? and db_table5.created>=?? and db_table5.created<?? and db_table6.created>=?? and db_table6.created<??",array(strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59')));
		}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// $where.=' and created>'.strtotime($_POST['start_time']).' and created<'.strtotime($_POST['end_time']);
		}
		if(!$isExport){
			if(isset($params['pageNo']) && isset($params['pageSize'])){			
				$pageNo=$_POST['pageNo']-1;
				$pageSize=$_POST['pageSize'];
			}else{
				$pageNo='1';
				$pageSize='20';
			}
		}
		if (!$isExport) {
			$data = $this -> Admin_model -> get_page_list($connar);
			// $data['ret']=$this -> Admin_model -> query('select * from(select id,dept_id,dept_name from db_admin '.$where.' limit '.$pageSize*$pageNo.','.$pageSize.') attend group by dept_id');
			// $data['ret']=$this -> Admin_model -> query('select * from(select id,dept_id,dept_name from db_admin '.$where.') attend  group by dept_id limit '.$pageSize*$pageNo.','.$pageSize);
			$data['ret']=$this -> Admin_model -> query('select * from(select distinct(db_admin.id),dept_id,dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.') attend  group by dept_id 
order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
			$count=$this -> Admin_model -> query('select count(*) count from (select * from (select distinct(db_admin.id),db_admin.dept_id,db_admin.dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.' ) attend group by dept_id
order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部"))a');
			$data['count']=$count[0]['count'];
			$data['totalPage']=ceil($data['count']/$pageSize);
		} else {
			$data['ret']=$this -> Admin_model -> query('select * from(select distinct(db_admin.id),dept_id,dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id
LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.') attend  group by dept_id 
order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
			$count=$this -> Admin_model -> query('select count(*) count from (select * from (select distinct(db_admin.id),db_admin.dept_id,db_admin.dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.' ) attend group by dept_id
order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部"))a');
		}
		foreach ($data['ret'] as $k => $v) {
			// 反馈次数身份条件筛选
			if(in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
				$where_role=' and 1=1';
			}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
				$where_role=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
			}else{
				$where_role=$this->sqlEscape(' and creator=?? ',array(getSess()['id']));
			}
			
			// 反馈次数
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where=' and feedback.created>'.strtotime($params['start_time']).' and feedback.created<'.strtotime($params['end_time']);
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$where=$this->sqlEscape(' and feedback.created>?? and feedback.created<??',array(strtotime($_POST['start_time']."00:00:00"),strtotime($_POST['end_time']."23:59:59")));
			}else{
				$where='';
			}
			$feedbackTime=$this -> Student_feedback_model->query("select count(*) feedbackTime from(select * from db_student_feedback union all select * from db_supervisor_feedback) feedback  left join db_admin on feedback.creator=db_admin.id where del_flag=".CommonStatus::UnDeleted[XPHEnum::Value]." and role_belong=".WebRole::Manage[XPHEnum::Value]." and feedback.status=".FeedBackStatus::HaveWrite[XPHEnum::Value]." and db_admin.dept_id=".$v['dept_id'].$where.$where_role);			

			$data['ret'][$k]['feedbackTime']=$feedbackTime[0]['feedbackTime'];
			
			// 其他材料次数身份条件筛选
			if(in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
				$where_role=' and 1=1';
			}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
				$where_role=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
			}else{
				$where_role=$this->sqlEscape(' and admin_id=?? ',array(getSess()['id']));
			}

			// 教学督导专题教研次数
			$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
			$connar1['join']=array('db_admin','db_admin.id=db_table1.admin_id');
			$connar1['items']='count(*) table1Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and db_table1.created>?? and db_table1.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(' and db_table1.created>?? and db_table1.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$connar1['where'].=$where_role;
			$table1Time=$this-> Table1_model->get_list_full($connar1);
			$data['ret'][$k]['table1Time']=$table1Time[0]['table1Time'];
			// 督导教学巡(检)查情况记录表次数
			$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
			$connar1['join']=array('db_admin','db_admin.id=db_table2.admin_id');
			$connar1['items']='count(*) table2Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and db_table2.created>?? and db_table2.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(' and db_table2.created>?? and db_table2.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$connar1['where'].=$where_role;
			$table2Time=$this-> Table2_model->get_list_full($connar1);
			$data['ret'][$k]['table2Time']=$table2Time[0]['table2Time'];
			// 督导巡考情况记录表次数
			$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
			$connar1['join']=array('db_admin','db_admin.id=db_table3.admin_id');
			$connar1['items']='count(*) table3Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and db_table3.created>?? and db_table3.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(' and db_table3.created>?? and db_table3.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$connar1['where'].=$where_role;
			$table3Time=$this-> Table3_model->get_list_full($connar1);
			$data['ret'][$k]['table3Time']=$table3Time[0]['table3Time'];
			// 教学状态专项检查表次数
			$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
			$connar1['join']=array('db_admin','db_admin.id=db_table4.admin_id');
			$connar1['items']='count(*) table4Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and db_table4.created>?? and db_table4.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(' and db_table4.created>?? and db_table4.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$connar1['where'].=$where_role;
			$table4Time=$this-> Table4_model->get_list_full($connar1);
			$data['ret'][$k]['table4Time']=$table4Time[0]['table4Time'];
			// 学生顶岗实习巡查次数
			$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
			$connar1['join']=array('db_admin','db_admin.id=db_table5.admin_id');
			$connar1['items']='count(*) table5Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and db_table5.created>?? and db_table5.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and db_table5.created>?? and db_table5.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$connar1['where'].=$where_role;
			$table5Time=$this-> Table5_model->get_list_full($connar1);
			$data['ret'][$k]['table5Time']=$table5Time[0]['table5Time'];
			// 教学信息员专题调查表次数
			$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
			$connar1['join']=array('db_admin','db_admin.id=db_table6.admin_id');
			$connar1['items']='count(*) table6Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and table6.created>?? and table6.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(' and table6.created>?? and table6.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$connar1['where'].=$where_role;
			$table6Time=$this-> Table6_model->get_list_full($connar1);
			$data['ret'][$k]['table6Time']=$table6Time[0]['table6Time'];
		}
		// 去除都为0的记录
// 		$ret=$data['ret'];
// 		$data['ret']=array();
// 		foreach ($ret as $k => $v) {
// 			// $break=false;
// 			// var_dump($v);
// 			if(!($v['feedbackTime']=='0' && $v['table1Time']=='0'  && $v['table2Time']=='0' && $v['table3Time']=='0' && $v['table4Time']=='0' && $v['table5Time']=='0' && $v['table6Time']=='0')){

// // var
// 				// pri($data['ret'][$k]);
// 				// unset($data['ret'][$k]);
// 				// pri($data['ret']);
// 				array_push($data['ret'], $v);
				
// 			}else{
// 				// var_dump('zl');
// 				// exit;
// 				$data['count']=$data['count']-1;
// 			}
// 			$data["totalPage"] = ceil($data["count"] / $params['pageSize']);
			
// 		}
// 		// 重置数组下标
// 		$data['ret']=array_values($data['ret']);
		if (!$isExport) {

			$dept_count=0;
			$feedbackTime_count=0;
			$table1_count=0;
			$table2_count=0;
			$table3_count=0;
			$table4_count=0;
			$table5_count=0;
			$table6_count=0;
			// var_dump($data['ret'][0]['feedbackTime']);
			foreach ($data['ret'] as $k => $v) {
				$feedbackTime_count=$feedbackTime_count+$v['feedbackTime'];
				$table1_count+=$v['table1Time'];
				$table2_count+=$v['table2Time'];
				$table3_count+=$v['table3Time'];
				$table4_count+=$v['table4Time'];
				$table5_count+=$v['table5Time'];
				$table6_count+=$v['table6Time'];
			}
			$count = count($data['ret']);

			$data['ret'][$count]['dept_name']= '合计';
			$data['ret'][$count]['feedbackTime']=$feedbackTime_count;
			$data['ret'][$count]['table1Time']=$table1_count;
			$data['ret'][$count]['table2Time']=$table2_count;
			$data['ret'][$count]['table3Time']=$table3_count;
			$data['ret'][$count]['table4Time']=$table4_count;
			$data['ret'][$count]['table5Time']=$table5_count;
			$data['ret'][$count]['table6Time']=$table6_count;
			
			echo json_encode($data);
		} else {
			// return $data["ret"];
			$dept_count=0;
			$feedbackTime_count=0;
			$table1_count=0;
			$table2_count=0;
			$table3_count=0;
			$table4_count=0;
			$table5_count=0;
			$table6_count=0;
			// var_dump($data['ret'][0]['feedbackTime']);
			foreach ($data['ret'] as $k => $v) {
				$feedbackTime_count=$feedbackTime_count+$v['feedbackTime'];
				$table1_count+=$v['table1Time'];
				$table2_count+=$v['table2Time'];
				$table3_count+=$v['table3Time'];
				$table4_count+=$v['table4Time'];
				$table5_count+=$v['table5Time'];
				$table6_count+=$v['table6Time'];
			}			
			array_push($data['ret'],array('title'=>'合计','dept_count'=>$count[0]['count'],'feedback_count'=>$feedbackTime_count,'table1_count'=>$table1_count,'table2_count'=>$table2_count,'table3_count'=>$table3_count,'table4_count'=>$table4_count,'table5_count'=>$table5_count,'table6_count'=>$table6_count));
			$this-> load ->library('PHPExcel/PHPExcel');
	        $excel = new PHPExcel();
	        //公共样式
	        $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
	        $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

	        $x_line=0;
	        $y_line=1;

			//标题
	        $title=array($_POST['start_time'].'至'.$_POST['end_time'],'学院','反馈次数','教学督导专题调研次数','督导教学巡(检)查情况记录表次数','督导巡考情况记录表次数','教学状态专项检查表次数','学生顶岗实习巡查次数','教学信息员专题调查表次数');
	        foreach ($title as $k => $v) {
	        	$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v);
	        	$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line)->getFont()->setBold(true);   
	        	$x_line++;
	        }

	        $x_line=0;
	        $y_line=2;
	        //正式数据
	        
	        foreach ($data['ret'] as $k => $v) {
	        unset($v['dept_id']);
	        	foreach ($v as $k1 => $v1) {
	        		if($k1=='id'){
	        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$_POST['start_time'].'至'.$_POST['end_time']);
	        		}else{
	        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v1);
	        		}
	        		
	        		$x_line++;
	        	}
	        	$x_line=0;
	        	$y_line++;
	        }
	        $write = new PHPExcel_Writer_Excel2007($excel); 
	       	$director = "upload/export/" . date('Ymd', time()) . "/";
	        if (!file_exists($director)) {
	            //检查是否有该文件夹，如果没有就创建，并给予最高权限
	            mkdir($director, 0777);
	        }        
			$file_name ='其他材料反馈报表'.date('Ymd').'.xlsx';       	
	       	$write->save($director.iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址',true,$director.$file_name));
		}
	}
}